import pandas as pd
import datetime

from sqlalchemy import create_engine, Column, Integer, String, Date, Double
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

file = r'C:\Users\metype\Desktop\company\AI 培训\副本备选问题列表汇总(托底版).xlsx'
# 建立数据库连接
db_url = "mysql+pymysql://root:123456@localhost:3308/ai_test"
engine = create_engine(db_url, echo=True)  # echo=True可用于显示SQL语句执行情况
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()


# 读取整个Excel文件
def readExcelColumn(keyword):
    df = pd.read_excel(file, sheet_name='汇总表')
    return df[keyword]  # 将'Column_Name'替换为你想要访问的列名


# 关键词添加
def saveKeyword(columnDatas):
    # 关键词 entity
    class aiQaaKeyword(Base):
        __tablename__ = 'ai_qaa_keyword'
        keyword_id = Column(Integer, primary_key=True)
        keyword = Column(String)
        del_flag = Column(Integer)
        create_date = Column(Date)

    for i in range(len(columnDatas)):
        if 0 < i < 200:
            for keyword in columnDatas[i].split('，'):
                new_aiQaaKeyword = aiQaaKeyword(keyword=keyword, del_flag=0, create_date=datetime.datetime.now())
                session.add(new_aiQaaKeyword)
                session.commit()


# 关键词添加
def saveKeywordMatchQuestion(columnDatas):
    class aiQaaKeyword(Base):
        __tablename__ = 'ai_qaa_keyword'
        keyword_id = Column(Integer, primary_key=True)
        keyword = Column(String)
        keyword_match_question_id = Column(Integer)
        del_flag = Column(Integer)
        create_date = Column(Date)

    # 关键词对应个问题 entity
    class aiQaaKeywordMatchQuestion(Base):
        __tablename__ = 'ai_qaa_keyword_match_question'
        keyword_match_question_id = Column(Integer, primary_key=True)
        question = Column(String)
        default_question = Column(Integer)
        deliver_weight = Column(Double)
        high_frequency_weight = Column(Double)
        del_flag = Column(Integer)
        create_date = Column(Date)

    # print(columnDatas)
    df = pd.DataFrame(columnDatas)
    insertId = 1
    for column_name, column_data in df.iloc[:199, :4].iterrows():
        new_aiQaaKeywordMatchQuestion = aiQaaKeywordMatchQuestion(keyword_match_question_id=insertId, question=column_data[0], default_question=column_data[3],
                                                                  deliver_weight=column_data[2] or 0.0, high_frequency_weight=0.0,
                                                                  del_flag=0, create_date=datetime.datetime.now())
        session.add(new_aiQaaKeywordMatchQuestion)
        session.commit()
        session.close()
        for keyword in column_data[1].split('，'):
            new_aiQaaKeyword = aiQaaKeyword(keyword=keyword, keyword_match_question_id=insertId, del_flag=0, create_date=datetime.datetime.now())
            session.add(new_aiQaaKeyword)
            session.commit()
            session.close()
        insertId += 1


def main():
    columnDatas = readExcelColumn(['问题描述', '关键词', '主动传递知识权重(0.0~1.0,一位小数)', '是否是默认问题'])
    saveKeywordMatchQuestion(columnDatas)



if __name__ == '__main__':
    main()